﻿using MiniExcelLibs;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.工厂.车间.报工
{
    public partial class Form硅钢投入产出表 : UIPage
    {
        int 开始行;
        int 结束行;
        string 工厂编号 = "2001";
        string FormWorkType = "硅钢";
        int 开始列;
        int 结束列;
        public Form硅钢投入产出表()
        {
            InitializeComponent();
        }

        private void Form硅钢投入产出表_Load(object sender, EventArgs e)
        {
            this.uiDatePicker开始日期.Value = DateTime.Today.AddDays(-1);
            this.uiDatePicker结束日期.Value = DateTime.Today.AddDays(-1);
            pub.ListRankAll(pub.全局工厂编号, this.uiComboBox查询等级);
            pub.ListWorkLineAll(pub.全局工厂编号, FormWorkType, this.uiComboBox查询产线);
            pub.ListWorkTeamAll(pub.全局工厂编号, "2001-01-01B02j1", this.uiComboBox查询班组);
            string 当前班组 = pub.Get员工所属班组(pub.sys_user.UserTrueName);
        }

        private void uiComboBox查询产线_SelectedIndexChanged(object sender, EventArgs e)
        {
            pub.ListWorkTeamAll(pub.全局工厂编号, this.uiComboBox查询产线.SelectedValue.ToString(), this.uiComboBox查询班组);

        }

        private void uiButton产出查询_Click(object sender, EventArgs e)
        {
            DateTime dtStar = DateTime.Now;
            Guid guid = Guid.NewGuid();
            DataTable tb = ListOutPutData(guid.ToString());
            //DataTable tb = ListData();
            uiDataGridView1.AutoGenerateColumns = false;
            this.uiDataGridView1.DataSource = tb;
            //this.uiDataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            this.uiDataGridView1.Refresh();
            for (int i = 0; i < this.uiDataGridView1.Rows.Count; i++)
            {
                if (this.uiDataGridView1.Rows[i].Cells["E产出等级"].Value.ToString() == "B")
                {
                    this.uiDataGridView1.Rows[i].Cells["E产出等级"].Style.BackColor = Color.FromArgb(255, 255, 128);
                }
                else if (this.uiDataGridView1.Rows[i].Cells["E产出等级"].Value.ToString() == "S")
                {
                    this.uiDataGridView1.Rows[i].Cells["E产出等级"].Style.BackColor = Color.FromArgb(255, 0, 0);
                }
            }
            deleteTable(guid.ToString());
            DateTime dtEnd = DateTime.Now;
            //MessageBox.Show($"开始时间：{dtStar.ToString()},结束时间：{dtEnd.ToString()}");
        }

        private DataTable ListOutPutData()
        {
            string guid = Guid.NewGuid().ToString();
            decimal 总投入重量 = 0;
            decimal 总产出重量 = 0;
            decimal 产出米数 = 0;
            decimal 总面积 = 0;
            string sql;
            sql = @"SELECT   row_number() over(order by 投入卷号) 序号, 投入卷号, 投入规格, 投入重量, 投入材质, 生产日期, 产出卷号, 产出规格, 产出材质, 产出重量, 产出等级, 去向, 
                   产线名称, 班组名称, 班别, CreateDate, 客户, 产地, 长度, 报工人, 报工时间, 是否抛送NC, 是否已打印, 修改人, 修改时间, 
                   理算厚度, 理算长度, 成品宽度, 实际宽度, 品名, 面积 from 
                    View_硅钢投入产出明细表 ";
            sql += " where 1=1  and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            sql += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            //string sql2 = "select isnull(sum(投入重量),0) ,isnull(sum(产出重量),0),isnull(sum(长度),0)  FROM      View_镀锌投入产出明细表 ";
            string sql2 = "SELECT distinct 投入卷号,投入重量 FROM View_硅钢投入产出明细表 ";
            sql2 += " where 1=1  and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            sql2 += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            if (this.uiComboBox查询产线.SelectedText != "全部")
            {
                sql += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
                sql2 += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
            }
            if (this.uiComboBox查询等级.SelectedText != "全部")
            {
                sql += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
                sql2 += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
            }
            if (this.uiComboBox查询班组.SelectedText != "全部" && !string.IsNullOrEmpty(this.uiComboBox查询班组.SelectedText))
            {
                sql += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
                sql2 += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
            }
            if (this.uiTextBox投入卷号包含.Text != "")
            {
                sql += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }
            if (this.uiTextBox产出卷号包含.Text != "")
            {
                sql += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }

            sql += " order by 报工时间,投入卷号";

            DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
            if (tb != null && tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    string 投入卷号 = tb.Rows[i]["投入卷号"].ToString();
                    string 投入重量 = tb.Rows[i]["投入重量"].ToString();
                    //等级
                    string 等级 = tb.Rows[i]["产出等级"].ToString();
                    if (等级.ToUpper().Contains("A") || 等级.ToUpper().Contains("B"))
                    {
                        decimal.TryParse(tb.Rows[i]["产出重量"].ToString(), out decimal a);
                        总产出重量 += a;
                    }
                    //decimal.TryParse(tb.Rows[i]["产出重量"].ToString(),out decimal a);
                    decimal.TryParse(tb.Rows[i]["长度"].ToString(), out decimal b);
                    //总产出重量 += a;
                    产出米数 += b;
                    decimal.TryParse(tb.Rows[i]["面积"].ToString(), out decimal 面积);
                    总面积 += 面积;
                }
            }

            DataTable tb2 = pub.GetDataTable(pub.ConnectionString, sql2);
            if (tb2 != null && tb2.Rows.Count > 0)
            {
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    decimal.TryParse(tb2.Rows[i]["投入重量"].ToString(), out decimal a);
                    总投入重量 += a;
                }
            }
            this.uiTextBox投入重量合计.Text = 总投入重量.ToString();
            this.uiTextBox产出重量合计.Text = 总产出重量.ToString();
            this.uiTextBox产出米数合计.Text = 产出米数.ToString();
            uiTextBox面积.Text = 总面积.ToString();

            if (总投入重量 > 0)
            {
                this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
            }
            else
            {
                this.uiTextBox产出率.Text = "";
            }

            this.uiTextBox卷数.Text = tb.Rows.Count.ToString();
            return tb;
        }

        private DataTable ListOutPutData(string guid)
        {
            decimal 总投入重量 = 0;
            decimal 总产出重量 = 0;
            decimal 产出米数 = 0;
            decimal 总面积 = 0;
            string sql;
            sql = @"SELECT  '" + guid + "', row_number() over(order by 投入卷号) 序号, 投入卷号, 投入规格, 投入重量, 投入材质, 生产日期, 产出卷号, 产出规格, 产出材质, 产出重量, 产出等级, 去向, ";
               sql += "    产线名称, 班组名称, 班别, CreateDate, 客户, 产地, 长度, 报工人, 报工时间, 是否抛送NC, 是否已打印, 修改人, 修改时间, ";
            sql += "    理算厚度, 理算长度, 成品宽度, 实际宽度, 品名, 面积 from ";
            sql += "     View_硅钢投入产出明细表 ";
            sql += " where 1=1  ";
            //sql += "and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            //sql += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            //string sql2 = "select isnull(sum(投入重量),0) ,isnull(sum(产出重量),0),isnull(sum(长度),0)  FROM      View_镀锌投入产出明细表 ";
            string sql2 = "SELECT distinct 投入卷号,投入重量 FROM View_硅钢投入产出明细表 ";
            sql2 += " where 1=1  and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            sql2 += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";
            if (string.IsNullOrWhiteSpace(uiTextBox客户.Text.Trim()) == false)
            {
                sql += " and 客户 like'%" + this.uiTextBox客户.Text.Trim() + "%' ";
                sql2 += " and 客户 like'%" + this.uiTextBox客户.Text.Trim() + "%' ";
            }
            if (this.uiComboBox查询产线.SelectedText != "全部")
            {
                sql += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
                sql2 += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
            }
            if (this.uiComboBox查询等级.SelectedText != "全部")
            {
                sql += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
                sql2 += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
            }
            if (this.uiComboBox查询班组.SelectedText != "全部" && !string.IsNullOrEmpty(this.uiComboBox查询班组.SelectedText))
            {
                sql += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
                sql2 += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
            }
            if (this.uiTextBox投入卷号包含.Text != "")
            {
                sql += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }
            if (this.uiTextBox产出卷号包含.Text != "")
            {
                sql += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }

            sql += " order by 报工时间,投入卷号";

            //塞入投入产出
            string sqlInsert = "insert into [dbo].[Bus_硅钢投入产出] ([guid], [序号], [投入卷号], [投入规格], [投入重量], [投入材质], [生产日期], [产出卷号], [产出规格], [产出材质], [产出重量], [产出等级], [去向], [产线名称], [班组名称], [班别], [CreateDate], [客户], [产地], [长度], [报工人], [报工时间], [是否抛送NC], [是否已打印], [修改人], [修改时间], [理算厚度], [理算长度], [成品宽度], [实际宽度], [品名], [面积])";
            sqlInsert += sql;
            pub.ExecuteSql(pub.ConnectionString, sqlInsert);

            //计算分摊重量
            sql = "  update a set a.产出汇总重量=b.产出汇总重量   from [Bus_硅钢投入产出] a ,(select 投入卷号 ,sum(产出重量) 产出汇总重量 ";
            sql += " from [Bus_硅钢投入产出] where guid='" + guid + "' group by 投入卷号 ) b  where a.投入卷号=b.投入卷号 and a.guid='" + guid + "' ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "     update [Bus_硅钢投入产出] set  分摊重量=投入重量*产出重量/产出汇总重量 where guid='" + guid + "'  ";
            pub.ExecuteSql(pub.ConnectionString, sql);


            sql = "select * from Bus_硅钢投入产出 where guid = '" + guid + "' and 生产日期 >= '" + this.uiDatePicker开始日期.Value.ToString("yyyy-MM-dd") + "' and 生产日期 < '" + uiDatePicker结束日期.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
            if (tb != null && tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    string 投入卷号 = tb.Rows[i]["投入卷号"].ToString();
                    string 投入重量 = tb.Rows[i]["投入重量"].ToString();
                    //等级
                    string 等级 = tb.Rows[i]["产出等级"].ToString();
                    if (等级.ToUpper().Contains("A") || 等级.ToUpper().Contains("B"))
                    {
                        decimal.TryParse(tb.Rows[i]["产出重量"].ToString(), out decimal a);
                        总产出重量 += a;
                    }
                    //decimal.TryParse(tb.Rows[i]["产出重量"].ToString(),out decimal a);
                    decimal.TryParse(tb.Rows[i]["长度"].ToString(), out decimal b);
                    //总产出重量 += a;
                    产出米数 += b;
                    decimal.TryParse(tb.Rows[i]["面积"].ToString(), out decimal 面积);
                    总面积 += 面积;
                }
            }

            DataTable tb2 = pub.GetDataTable(pub.ConnectionString, sql2);
            if (tb2 != null && tb2.Rows.Count > 0)
            {
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    decimal.TryParse(tb2.Rows[i]["投入重量"].ToString(), out decimal a);
                    总投入重量 += a;
                }
            }
            this.uiTextBox投入重量合计.Text = 总投入重量.ToString();
            this.uiTextBox产出重量合计.Text = 总产出重量.ToString();
            this.uiTextBox产出米数合计.Text = 产出米数.ToString();
            uiTextBox面积.Text = 总面积.ToString();

            if (总投入重量 > 0)
            {
                this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
            }
            else
            {
                this.uiTextBox产出率.Text = "";
            }

            this.uiTextBox卷数.Text = tb?.Rows.Count.ToString();
            return tb;
        }

        private void 硅钢投入产出分摊(string guid, DateTime value, DateTime dateTime)
        {
            string sql = "";
            sql = "insert into Temp_投入产出表(guid, 产线名称, 产出日期, 投入卷号, 产出卷号,   产出总重量  )";
            sql += " select '" + guid + "' , Bus_SiOutput_4.WorkLineName 产线, " +
                " Bus_SiOutput_4.OutputDate 产出日期, Bus_SiOriginal_4.OriginalVolumeNew 投入卷号, Bus_SiOutput_4.OutputVolumeNew 产出卷号," +
                " Bus_SiOutput_4.OutputWeight/1000 AS 产出重量   " +
                " FROM      dbo.Bus_SiOutput AS Bus_SiOutput_4 INNER JOIN  dbo.Bus_SiOriginal AS Bus_SiOriginal_4 ON  " +
                " Bus_SiOutput_4.guid = Bus_SiOriginal_4.guid   ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.投入重量 =b.OriginalWeight/1000 from   Temp_投入产出表 a ,Bus_SiOriginal b where a.投入卷号=b.OriginalVolumeNew and a.guid='" + guid + "' ";

            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A1 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'" +
                " and b.OutputQualityGrade ='A1'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出A2 =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'" +
                    " and b.OutputQualityGrade ='A2'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出B =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'" +
                  " and b.OutputQualityGrade ='B'";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "update a set a.产出s =b.OutputWeight/1000 from   Temp_投入产出表 a ,Bus_SiOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'" +
                   " and b.OutputQualityGrade ='S'";
            pub.ExecuteSql(pub.ConnectionString, sql);

            sql = "update a set a.汇总面积 =CONVERT(decimal(18, 0),b.OutputWidth)*CONVERT(decimal(18, 0),b.OutputMeter)/1000 from   Temp_投入产出表 a ,Bus_SiOutput b where a.产出卷号=b.OutputVolumeNew and a.guid='" + guid + "'";
            pub.ExecuteSql(pub.ConnectionString, sql);
        }

        private void deleteTable(string guid)
        {
            string sql = "delete from Bus_硅钢投入产出 where guid = '" + guid + "';delete from Temp_投入产出表 where guid = '" + guid + "';";
            pub.ExecuteSql(pub.ConnectionString, sql);
        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;
            string guid = Guid.NewGuid().ToString();
            DataTable tb = ListOutPutData(guid);
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, tb, true, "sheet1", ExcelType.XLSX);
                    deleteTable(guid);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
            deleteTable(guid.ToString());
        }

        private void uiDataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            开始行 = e.RowIndex;
            开始列 = e.ColumnIndex;
        }

        private void uiDataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            decimal 总投入重量 = 0;
            decimal 投入重量 = 0;
            decimal 总产出重量 = 0;
            decimal 总产面积 = 0;
            结束列 = e.ColumnIndex;

            string guid = Guid.NewGuid().ToString();
            int 总卷数 = 0;
            结束行 = e.RowIndex;

            if (开始行 == 0 && 结束行 == 0)
            {

                this.uiTextBox卷数.Text = "1";
                this.uiTextBox投入重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["E投入重量"].Value.ToString();
                this.uiTextBox产出重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["E产出重量"].Value.ToString();
                this.uiTextBox面积.Text = this.uiDataGridView1.Rows[开始行].Cells["E面积"].Value.ToString();
            }

            if (开始行 >= 0 && 结束行 > 0)
            {
                if (开始行 > 结束行)
                {
                    int k = 开始行;
                    开始行 = 结束行;
                    结束行 = k;
                }
                string sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");
                for (int j = 0; j < uiDataGridView1.Rows.Count; j++)
                {
                    //取消之前的选择
                    this.uiDataGridView1.Rows[j].Cells["E选择"].Value = false;
                }
                for (int i = 开始行; i <= 结束行; i++)
                {
                    //选择

                    this.uiDataGridView1.Rows[i].Cells["E选择"].Value = true;

                    投入重量 = pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E投入重量"].Value.ToString());
                    string 投入卷号 = this.uiDataGridView1.Rows[i].Cells["E投入卷号"].Value.ToString();
                    sql = " insert into Temp_投入汇总表 (guid, 卷号, 投入重量) ";
                    sql += " values('" + guid + "','" + 投入卷号 + "','" + 投入重量 + "')";
                    pub.ExecuteSql(pub.ConnectionString, sql, "insert into Temp_投入汇总表 ");

                    总产出重量 += pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E产出重量"].Value.ToString());
                    总产面积 += pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E面积"].Value.ToString());
                    总卷数++;
                }
                sql = "select  isnull(  sum(t.投入重量),0)  from  (select distinct 卷号, 投入重量 from Temp_投入汇总表 where guid='" + guid + "' ) t ";
                DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
                if (tb.Rows.Count > 0)
                {
                    总投入重量 = pub.Todecimal(tb.Rows[0][0].ToString());
                }
                else
                {
                    总投入重量 = 0;
                }
                sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");

                if (总投入重量 != 0)
                {
                    this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
                }
                else
                {
                    this.uiTextBox产出率.Text = "";
                }
                this.uiTextBox卷数.Text = 总卷数.ToString();
                this.uiTextBox投入重量合计.Text = Math.Round(总投入重量, 3).ToString();
                this.uiTextBox产出重量合计.Text = Math.Round(总产出重量, 3).ToString();
                this.uiTextBox面积.Text = Math.Round(总产面积, 3).ToString();
            }
        }
    }
}
